declare @HoP table
(
	 ID int,
	ChamberName varchar(25) 
);
insert into @HoP
values( 0, 'House of Representatives');
insert into @HoP
values( 1, 'Senate');

begin tran t1;

delete Parl2.dbo.ParliamentarianPositions;
delete Parl2.dbo.ElectorateOffices;
delete Parl2.dbo.Positions;
delete Parl2.dbo.Parliamentarians;

insert into Parl2.dbo.Parliamentarians

	Select 
		m.RecID,
		m.MPSalutationTitle,
		m.MPFirstName,
		m.MPSurname,
		m.MPHonorific,
		m.MPElectorate,
		m.MPState,
		m.MPPolParty,
		h.ChamberName,
		m.MPTelephone,
		m.MPFax,
		m.MPInternet,
		null, null, null, null,
		m.MPNameID,
		m.MPFormer,
		m.MPGender
	From CPALaph.dbo.tblMPsInformation as m
		left join @HoP as h on m.MPType = h.ID;

set IDENTITY_INSERT Parl2.dbo.Positions ON;
insert into Parl2.dbo.Positions
(Id, FullTitle)
select
	ti.RecID,
	ti.TitleInfoFull
from CPALaph.dbo.tblTitleInformation as ti;
set IDENTITY_INSERT Parl2.dbo.Positions OFF;

Insert into Parl2.dbo.ParliamentarianPositions
	select t.RecID, t.Title
	from CPALaph.dbo.tblTitle as t join 
			CPALaph.dbo.tblTitleInformation as ti
			on t.Title = ti.RecID
	where t.RecID is not null AND  t.Title is not null AND ti.TitleInfoFull is not null;

select 
	o.ElectorateAddressLine1,
	o.ElectorateAddressLine2,
	o.ElectorateSuburb,
	o.ElectorateState,
	o.ElectoratePostcode,
	o.ElectoratePrincipalOffice,
	o.ElectorateTelephone,
	o.ElectorateFax,
	o.ElectoratePostAddress,
	o.ElectoratePostSuburb,
	o.ElectoratePostState,
	o.ElectoratePostPostcode,
	IDENTITY(int, 1, 1) as Id,
	o.RecID
	into #ElectorateOffices_temp
from CPALaph.dbo.tblElectorateOfficeDetails as o

SET IDENTITY_INSERT Parl2.dbo.ElectorateOffices ON;
insert into Parl2.dbo.ElectorateOffices
(AddressLine1,AddressLine2,Suburb,State,PostCode,PrincipalOffice,
 Telephone,Fax,PostalAddress,PostalSuburb,PostalState,PostalPostCode,
 Id,Parliamentarian_CPALId)
	select * from #ElectorateOffices_temp;
Set IDENTITY_INSERT Parl2.dbo.ElectorateOffices OFF;

DROP table #ElectorateOffices_temp;
	
commit tran t1;